package com.huawei.codearts.service;

import com.huawei.codearts.dao.MemberDao;
import com.huawei.codearts.model.Member;
import com.huawei.codearts.tools.utils.JDBCUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class MemberService {

    /** 单条数据 */
    public static Object getMember(int id) {

        String sql = "SELECT id, user, name, status, money, createtime FROM qhdzs_member where id=?";
        Member member = MemberDao.getMember(sql, id);
        // 处理数据
        int status = member.getStatus();
        String statusStr = status == 1 ? "正常" : (status == 2 ? "未验证" : "禁用");
        member.setStatusStr(statusStr);
        // 时间戳转换成日期
        long createtime = member.getCreatetime();
        Date date = new Date(createtime * 1000);  // 把秒转换成毫秒
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String createtimeFormat = sdf.format(date);
        member.setCreatetimeFormat(createtimeFormat);

        // 自定义map
        HashMap<String, Object> dataMap = new HashMap<>();
        dataMap.put("id", member.getId());
        dataMap.put("user", member.getUser());
        dataMap.put("name", member.getName());
        dataMap.put("status", member.getStatus());
        dataMap.put("money", member.getMoney());
        dataMap.put("createtimeFormat", createtimeFormat);
        dataMap.put("statusStr", statusStr);

        return dataMap;
    }

    /** 多条数据 */
    public static Object getMemberList() {

        // 返回数据
        List<Object> dataList = new ArrayList<>();
        // 从数据库获取的数据
        List<Member> memberList = MemberDao.getMemberList();
        int i = 0;
        for(Member item : memberList) {
            i ++;
            // 处理数据
            int status = item.getStatus();
            String statusStr = status == 1 ? (i == 1 ? "正常" : "正常" + i) : (status == 2 ? "未验证" : "禁用");
            item.setStatusStr(statusStr);
            // 时间戳转换成日期
            long createtime = item.getCreatetime();
            Date date = new Date(createtime * 1000);  // 把秒转换成毫秒
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String createtimeFormat = sdf.format(date);
            item.setCreatetimeFormat(createtimeFormat);

            // 定义map
            HashMap<String, Object> dataMap = new HashMap<>();
            dataMap.put("id", item.getId());
            dataMap.put("user", item.getUser());
            dataMap.put("name", item.getName());
            dataMap.put("status", item.getStatus());
            dataMap.put("money", item.getMoney());
            dataMap.put("createtimeFormat", createtimeFormat);
            dataMap.put("statusStr", statusStr);
            dataList.add(dataMap);
        }

        return dataList;
    }

    /** 添加 */
    public static Boolean useradd() {
        Member user = new Member();
        user.setUser("qwer@qq.com");
        user.setName("a100000");
        user.setStatus(1);
        user.setMoney(BigDecimal.valueOf(968));
        long timestamp = System.currentTimeMillis();
        user.setCreatetime(timestamp);
        return MemberDao.insert(user);
    }

    /** 更新 */
    public static Boolean update(int id) {
        Member user = new Member();
        user.setId(id);
        user.setUser("qwer-g@qq.com");
        user.setName("a200000");
        user.setMoney(BigDecimal.valueOf(1968));
        long timestamp = System.currentTimeMillis();
        user.setCreatetime(timestamp);
        return MemberDao.update(user);
    }

    /** 删除 */
    public static boolean del(int id) {
        return MemberDao.del(id);
    }

    /** 数据库事务 & 锁 */
    public static boolean updateWithTx(int id) throws Exception {
        boolean resbool = false;
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
//            System.out.println(conn.getAutoCommit()); // true
            //1.取消数据的自动提交
            conn.setAutoCommit(false);

            /*
             * 也可以添加分布式锁 比如redis的
             * set money = ? 需要加锁 比如：排他锁 FOR UPDATE 或者 乐观锁
             * set money = money - 100 这种已经是锁了
             * 乐观锁是不用加锁 谁都能读 但在更新的时候加上version条件 如下：
             * update qhdzs_member set money = ?, version = version + 1 where id = ? and version = $version
             */
            String sql = "SELECT id, user, name, status, money, createtime FROM qhdzs_member where id=? FOR UPDATE";  // 排他锁 FOR UPDATE
            Member member = MemberDao.getMemberTx(conn, sql, id);
            BigDecimal money = member.getMoney();
            BigDecimal sum = money.add(BigDecimal.valueOf(50));
            System.out.println("member1:" + member);
            System.out.println("sum1:" + sum);

            Thread.sleep(8000);

            String sql0 = "update qhdzs_member set money = ? where id = ?";  // 这种赋值形式 就需要查询的时候加锁 比如上面的 FOR UPDATE 或者乐观锁
            int res0 = MemberDao.updateWithTx(conn, sql0, sum, id);
            System.out.println("res0: " + res0);

            String sql1 = "update qhdzs_member set money = money - 100 where id = ?";
            int res1 = MemberDao.updateWithTx(conn, sql1, id);
            System.out.println("res1: " + res1);

            Thread.sleep(5000);
            // 模拟网络异常
//            System.out.println(10 / 0);

            String sql2 = "update qhdzs_member set money = money + 100 where id = ?";
            int res2 = MemberDao.updateWithTx(conn, sql2, 49);
            System.out.println("res2: " + res2);

            //2.提交数据
            if (res1 > 0 && res2 > 0) {
                System.out.println("转账成功+++");
                conn.commit();
                resbool = true;
            } else {
                try {
                    System.out.println("转账失败---");
                    conn.rollback();
                } catch (SQLException e2) {
                    e2.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            //3.回滚数据
            try {
                // 发生异常时回滚事务
                if (conn != null) {
                    conn.rollback();
                    System.out.println("事务已回滚");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        } finally {
            // 链接是我们在外面造的，咱就关掉，而preparedstatement是里面造的就在里面关
            JDBCUtils.releaseDb(null, null, conn);
        }

        return resbool;
    }

    /** 数据库事务2 & 锁 */
    public static boolean updateWithTx2(int id) throws Exception {
        boolean resbool = false;
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
//            System.out.println(conn.getAutoCommit()); // true
            //1.取消数据的自动提交
            conn.setAutoCommit(false);

            String sql = "SELECT id, user, name, status, money, createtime FROM qhdzs_member where id=? FOR UPDATE";  // 排他锁 FOR UPDATE
            Member member = MemberDao.getMemberTx(conn, sql, id);
            BigDecimal money = member.getMoney();
            BigDecimal sum = money.add(BigDecimal.valueOf(50));
            System.out.println("member11:" + member);
            System.out.println("sum11:" + sum);

            String sql0 = "update qhdzs_member set money = ? where id = ?";
            int res0 = MemberDao.updateWithTx(conn, sql0, sum, id);
            System.out.println("res00: " + res0);

            String sql1 = "update qhdzs_member set money = money - 100 where id = ?";
            int res1 = MemberDao.updateWithTx(conn, sql1, id);
            System.out.println("res11: " + res1);

            //模拟网络异常
//            System.out.println(10 / 0);

            String sql2 = "update qhdzs_member set money = money + 100 where id = ?";
            int res2 = MemberDao.updateWithTx(conn, sql2, 49);
            System.out.println("res22: " + res2);

            //2.提交数据
            if (res1 > 0 && res2 > 0) {
                System.out.println("转账成功2+++");
                conn.commit();
                resbool = true;
            } else {
                try {
                    System.out.println("转账失败---");
                    conn.rollback();
                } catch (SQLException e2) {
                    e2.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            //3.回滚数据
            try {
                // 发生异常时回滚事务
                if (conn != null) {
                    conn.rollback();
                    System.out.println("事务已回滚");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        } finally {
            // 链接是我们在外面造的，咱就关掉，而preparedstatement是里面造的就在里面关
            JDBCUtils.releaseDb(null, null, conn);
        }

        return resbool;
    }
}
